package com.dzzw.Dao;

import com.dzzw.Model.Vacation;
import com.dzzw.utils.DButils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//假单
public class VacationDao {
    private final Connection con = DButils.getCon();
    //新增假单申请
    public void VacationAdd(Vacation vacation){
        String sql = "insert into t_vacation(employee_id,leader_id,vacation_reason,audit_status,vacation_starttime,vacation_endtime) value(?,?,?,?,?,?)";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, vacation.getEmployee_id());
            preparedStatement.setInt(2, vacation.getLeader_id());
            preparedStatement.setString(3, vacation.getVacation_reason());
            preparedStatement.setInt(4, 0);
            preparedStatement.setString(5, vacation.getVacation_starttime());
            preparedStatement.setString(6, vacation.getVacation_endtime());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据添加失败！");

            e.printStackTrace();
        }
    }

    //根据部门id——假单审批通过1
    public void VacationPass(Vacation vacation){
        String sql = "update t_vacation set audit_opinion= ?,audit_status = ? where vacation_id = ? ";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, vacation.getAudit_opinion());
            preparedStatement.setInt(2, 1);
            preparedStatement.setInt(3, vacation.getVacation_id());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }

    //根据部门id——假单审批未通过2
    public void VacationNoPass(Vacation vacation){
        String sql = "update t_vacation set audit_opinion= ?,audit_status = ? where vacation_id = ? ";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, vacation.getAudit_opinion());
            preparedStatement.setInt(2, 2);
            preparedStatement.setInt(3, vacation.getVacation_id());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }

    //根据leader_id——查询全部待审批的假单
    public static List<Vacation> SelectVacationByLeaderId(int leader_id) {

        List<Vacation> list = new ArrayList<>();
        String sql = "SELECT v.vacation_id,e.employee_name,l.leader_name,v.vacation_reason,v.audit_status,v.vacation_starttime,v.vacation_endtime FROM t_vacation AS v INNER JOIN t_leader AS l ON v.leader_id = l.leader_id INNER JOIN t_employee AS e ON e.leader_id = l.leader_id AND v.employee_id = e.employee_id where v.leader_id = ?";
        try{
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,leader_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Vacation vacation = new Vacation();
                vacation.setVacation_id(resultSet.getInt(1));
                vacation.setEmployee_name(resultSet.getString(2));
                vacation.setLeader_name(resultSet.getString(3));
                vacation.setVacation_reason(resultSet.getString(4));
                vacation.setAudit_status(resultSet.getInt(5));
                vacation.setVacation_starttime(resultSet.getString(6));
                vacation.setVacation_endtime(resultSet.getString(7));
                list.add(vacation);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch(Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }

    //根据employee_id查询全部申请信息
    public static List<Vacation> SelectVacationByEmployeeId(int employee_id) {
        List<Vacation> list = new ArrayList<>();
        String sql = "SELECT v.vacation_id,e.employee_name,l.leader_name,v.vacation_reason,v.audit_opinion,v.audit_status,v.vacation_starttime,v.vacation_endtime FROM t_vacation AS v INNER JOIN t_leader AS l ON v.leader_id = l.leader_id INNER JOIN t_employee AS e ON e.leader_id = l.leader_id AND v.employee_id = e.employee_id where v.employee_id = ?";
        try{
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,employee_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Vacation vacation = new Vacation();
                vacation.setVacation_id(resultSet.getInt(1));
                vacation.setEmployee_name(resultSet.getString(2));
                vacation.setLeader_name(resultSet.getString(3));
                vacation.setVacation_reason(resultSet.getString(4));
                vacation.setAudit_opinion(resultSet.getString(5));
                vacation.setAudit_status(resultSet.getInt(6));
                vacation.setVacation_starttime(resultSet.getString(7));
                vacation.setVacation_endtime(resultSet.getString(8));
                list.add(vacation);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch(Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }


}
